//To aggregate new data to n3m
clear all
set more off

log using DataLog_n3m_2018_A, replace

qui use GU15_18_Q, replace
keep if cycle==180 | cycle==184 | cycle==188 | cycle==192

qui rename bmf_adj_occ_weight ADJ_WT

drop if missing(n3)
drop if missing(mog)

sort cycle n3 mog
by cycle n3 mog, sort: egen wd=sum(ADJ_WT) //sum of all weights in each category, i.e., demoninator in the weighted average calculation 
foreach a of varlist estoccwt HOURS_WORKED ahr CQBENC* cqtotben {
qui egen n3m_`a'=sum(`a'*ADJ_WT/wd), by(cycle n3 mog) 
}
keep cycle n3 mog n3m_*
collapse n3m_*, by(cycle n3 mog)

sort mog
save masterfile, replace

qui use mog_num, clear
sort mog
save usefile, replace

qui use masterfile, clear
merge m:1 mog using usefile
sort _merge
drop if _merge<3
drop _merge

qui gen n3mog=n3*100+mognum

//Match cycle with yrq
sort cycle
save masterfile, replace

qui use cycle_yrq, clear
sort cycle
save usefile, replace

qui use masterfile, clear
merge m:1 cycle using usefile
sort _merge
drop if _merge<3
drop _merge

save GU15_18_A_n3m, replace 

//Append OLD data
append using GU82_14_A_n3m_ur_all_NOfen3_2017
save GU82_18_A_n3m, replace 

//=========to make sure only real n3 shows up==========
sort n3
save masterfile, replace

qui use naics2002_n3, clear 
sort n3
save usefile, replace

qui use masterfile, clear
merge m:1 n3 using usefile
sort _merge
drop if _merge<3
drop _merge

//n3, n2, n1
drop if missing(n3)

//========to make sure only real mog shows up===========
sort mognum
save masterfile, replace

qui use mognum_2017, clear 
rename j_mognum mognum
sort mognum
save usefile, replace

qui use masterfile, clear
merge m:1 mognum using usefile
sort _merge
drop if _merge<3
drop _merge

drop if missing(mognum)

save GU82_18_A_n3m, replace 

///macro data
sort year
save masterfile, replace

qui use deflators_n_ur_2018, clear
sort year
save usefile, replace

qui use masterfile, clear
merge m:1 year using usefile, update replace
sort _merge
drop if _merge<3
drop _merge

save GU82_18_A_n3m, replace 

//===========================Calculate benefit groups================================================

//Hourly: (i) Basic earnings (ii) Total earnings (basic earnings plus overtime, bonuses etc.)  (iii) total compensation (total earnings + all benefits other than those already included in total earnings measure)
//D. (iii) broken down by benefit categories (quasi-fixed vs. other benefits; legally required vs. discretionary benefits that can be adjusted)

//qui gen j_1=j_ahr //already exists
qui replace n3m_comp=n3m_ahr+n3m_cqtotben //hourly total compensation already exists

//Quarterly total: (i) Basic earnings (ii) Total earnings (basic earnings plus overtime, bonuses etc.)  (iii) total compensation (total earnings + all benefits other than those already included in total earnings measure)
//D. (iii) broken down by benefit categories (quasi-fixed vs. other benefits; legally required vs. discretionary benefits that can be adjusted)

//Make them real:
qui replace n3mog=n3*100+mognum

qui tsset n3mog year, yearly 
sort n3mog year

//CPI all/GNP df
foreach a of varlist n3m_comp n3m_cqtotben n3m_ahr { 
qui replace log`a'=log(`a'/cpi)
qui replace logdiff`a'=log`a'-l.log`a'
}

save GU82_18_A_n3m, replace 

log close
